<?php
/**
 * Created by PhpStorm.
 * User: g
 * Date: 2018/1/3
 * Time: 11:54
 */

namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Tifyzfcl extends Common
{
    public function index(){
        $mdbh = $this->request->get('mdbh');
        $this->assign('mdbh',$mdbh);
        $fq = $this->request->get('fq');
        $this->assign('fq',$fq);
        $sf = $this->request->get('sf');
        $this->assign('sf',$sf);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);

        $where = [];
        $where['tax'] = ['exp','> `yzfje`'];
        $where['status'] = 7;
        if($mdbh){
            $where['sn'] = ['like',"%{$mdbh}%"];
        }
        if($fq){
            $fqids = db('cg_market_zoning')->where('title','like',"%{$fq}%")->column('id');
            $jxsids = db('cg_jxs')->where('zoning','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sf){
            $fqids = db('cg_market_provice')->where('title','like',"%{$sf}%")->column('id');
            $jxsids = db('cg_jxs')->where('provice','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sqr){
            $sqrids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['cuser'] = ['in',$sqrids];
        }
        if($start&&$end){
            $where['ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['ctime'] = ['>',strtotime($end)];
        }
        $cnt = db('cg_ti_hexiao')->where($where)->count();
        $this->assign('cnt',$cnt);
        $data = db('cg_ti_hexiao')->where($where)->paginate(10,$cnt);
        $page = $data->render();
        $data = $data->items();

        $tax = 0;
        foreach($data as $k=>$v){
            $v['cuser_name'] = db('employee')->where('id',$v['cuser'])->value('em_name');
            $v['jxs_name'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $data[$k] = $v;
            $tax += $v['tax'];
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        $this->assign('tax',$tax);
//        echo db('cg_ti_hexiao')->getLastSql();
        return $this->fetch();
    }

    //常规
    public function indexcg(){
        $mdbh = $this->request->get('mdbh');
        $this->assign('mdbh',$mdbh);
        $fq = $this->request->get('fq');
        $this->assign('fq',$fq);
        $sf = $this->request->get('sf');
        $this->assign('sf',$sf);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);

        $where = [];
        $where['tax'] = ['exp','> `yzfje`'];
        if($mdbh){
            $where['no'] = ['like',"%{$mdbh}%"];
        }
        if($fq){
            $fqids = db('cg_market_zoning')->where('title','like',"%{$fq}%")->column('id');
            $jxsids = db('cg_jxs')->where('zoning','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sf){
            $fqids = db('cg_market_provice')->where('title','like',"%{$sf}%")->column('id');
            $jxsids = db('cg_jxs')->where('provice','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sqr){
            $sqrids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['c_user_id'] = ['in',$sqrids];
        }
        if($start&&$end){
            $where['created_on'] = ['between',[date('Y-m-d H:i:s',strtotime($start)),date('Y-m-d H:i:s',strtotime($end))]];
        }elseif($start){
            $where['created_on'] = ['>',date('Y-m-d H:i:s',strtotime($start))];
        }elseif($end){
            $where['created_on'] = ['>',date('Y-m-d H:i:s',strtotime($end))];
        }
        $cnt = db('ti_hexiao_apply')->where($where)->count();
        $this->assign('cnt',$cnt);
        $data = db('ti_hexiao_apply')->where($where)->field('no sn,c_user_id,jxs_id,id,tax,yzfje,created_on')->paginate(10,$cnt);
        $page = $data->render();
        $data = $data->items();
//        echo db('ti_hexiao_apply')->getLastSql();
        $tax = 0;
        foreach($data as $k=>$v){
            $v['cuser_name'] = db('employee')->where('id',$v['c_user_id'])->value('em_name');
            $v['jxs_name'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $data[$k] = $v;
            $tax += $v['tax'];
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        $this->assign('tax',$tax);
//        echo db('cg_ti_hexiao')->getLastSql();
        return $this->fetch();
    }

    //录入--非常规和进场
    public function input(){
        $id = $this->request->param('id');
        if($_POST){
           // echo 1;die;
            $data = Request::instance()->post();
            //
            $valid = new Valid([
                'zfpz|支付凭证'=>'require',
                'zfr|支付人'=>'require',
                'zfsj|支付时间'=>'require|dateFormat:Y-m-d',
                'bczfje|本次支付金额'=>'require',
            ]);
            if(false==$valid->check($data)){
                echo json_encode(['success'=>false,'msg'=>$valid->getError()]);die;
            }

            //验证剩余支付金额
            $yzfje = db('cg_ti_hexiao')->where('id',$id)->value('yzfje');
            $tax = db('cg_ti_hexiao')->where('id',$id)->value('tax');

            $bhje = db('cg_ti_hexiao_list')
                ->where([
                    'zhifufangshi'=>13,
                    'hx_id'=>$id
                ])
                ->sum('shijitourujine');

            if($data['bczfje']>($tax-$yzfje-$bhje)){
                echo json_encode(['success'=>false,'msg'=>'本次支付金额不能大于可支付金额']);die;
            }
            $data['hx_id'] = $id;
            $indata=["hx_id"=>$id,"zfpz"=>$data["zfpz"],"zfr"=>$data["zfr"],"zfsj"=>$data["zfsj"],"bczfje"=>$data["bczfje"],"zfbz"=>$data["zfbz"]];
            try{
                 $bool = db('cg_ti_hexiao_zhifu')->insertGetId($indata);
             }catch(\Exception $e){
                echo json_encode(['success'=>false,'msg'=>$e->getMessage()]);die;
            }
          // echo json_encode(['success'=>false,'msg'=>"aaa"]);die;
             
            $yuande=Db::table("cg_ti_hexiao_list")->alias("ahd")
            ->join("cg_ti_hexiao ahp","ahd.hx_id=ahp.id")
            ->join("cg_ti_list apd","ahd.list_id=apd.id")
            ->join("cg_ti ap","apd.ti_id=ap.id")
            ->where("ahp.id=$id")
            ->field("ahd.id,ahp.sn as ahpno,ahd.sn as ahdno,ap.sn as apno,apd.sn as apdno,apd.id as apdid,apd.jxs_id,ahd.hanshuihexiaojine as hanshui,ahd.zhifufangshi,ahd.isVAT,ahd.weishuihexiaojine,ahd.shuijin")
            ->select();
            $bili=$data['bczfje']/($tax-$bhje);
            $listdata=[];
                for($i=0;$i<count($yuande);$i++){
                    if($yuande[$i]["zhifufangshi"]!=13){
                              $tmp["type"]=2;$tmp["zf_id"]=$bool;$tmp["hx_id"]=$yuande[$i]["id"];$tmp["he_no"]=$yuande[$i]["ahpno"];
                        $tmp["he_de_no"]=$yuande[$i]["ahdno"];$tmp["apply_detail_id"]=$yuande[$i]["apdid"];$tmp["ap_no"]=$yuande[$i]["apno"];
                        $tmp["ap_de_no"]=$yuande[$i]["apdno"];$tmp["jxs"]=$yuande[$i]["jxs_id"];$tmp["zfren"]=$data["zfr"];
                        $tmp["bcjine"]=$yuande[$i]["hanshui"]*$bili;$tmp["zfday"]=$data["zfsj"];$tmp["pinzheng"]=$data["zfpz"];
                        $tmp["dikou"]=$yuande[$i]["isVAT"];$tmp["weishui"]=$yuande[$i]["weishuihexiaojine"]*$bili;$tmp["shuijin"]=$yuande[$i]["shuijin"]*$bili;
                        for($j=0;$j<count($data["did"]);$j++){
                            if($yuande[$i]["id"]==$data["did"][$j]){
                                $tmp["zfid"]=$data["paymethod"][$j];
                                break;
                            }
                        }
                        $listdata[]=$tmp;
                    }
                  
                }
           // echo json_encode(['success'=>false,'msg'=>"aaa"]);die;
            if($bool){
                db('cg_ti_hexiao')->where('id',$id)->setInc('yzfje',$data['bczfje']);
                //新增部分
                Db::table("apti_zhifu_list")->insertAll($listdata);
                echo json_encode(['success'=>true,'msg'=>'ok']);
            }else{
                echo json_encode(['success'=>false,'msg'=>'数据库错误']);
            }
            die;
        }

        //查询核销基本信息
        $basic = db('cg_ti_hexiao')->where('id',$id)->find();
        $basic['ctime'] = date('Y-m-d H:i',$basic['ctime']);
        $basic['cuser_name'] = db('employee')->where('id',$basic['cuser'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['cuser'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['cuser']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        //补货及补费中金额
        $basic['bhje'] = db('cg_ti_hexiao_list')
            ->where([
                'zhifufangshi'=>13,
                'hx_id'=>$id
                ])
            ->sum('shijitourujine');
        $basic['kzfje'] = $basic['tax']-$basic['yzfje']-$basic['bhje'];
        $this->assign('basic',$basic);

        $list = db('cg_ti_hexiao_list t')->join('cg_ti_list t2','t2.id=t.list_id')->join('cg_ti t3','t3.id=t2.ti_id')->where('t.hx_id',$id)->field('*,t.sn tsn,t2.sn t2sn,t3.sn t3sn,t.id tid,t2.id t2id,t3.id t3id,t2.cuser ysqr,t.zhifufangshi')->select();
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['zhifufangshi'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }
        $this->assign('shijitourujine',$shijitourujine);
        $this->assign('yihexiaojine',$yihexiaojine);
        $this->assign('weishuihexiaojine',$weishuihexiaojine);
        $this->assign('shuijin',$shuijin);
        $this->assign('hanshuihexiaojine',$hanshuihexiaojine);
        $this->assign('shijitourujine2',$shijitourujine2);
        $this->assign('list',$list);

        //支付信息
        $zfxx = db('cg_ti_hexiao_zhifu')->where('hx_id',$id)->select();
        foreach($zfxx as $k=>$v){
            $v['zfr'] = db('employee')->where('id',$v['zfr'])->value('em_name');
            $zfxx[$k] = $v;
        }
        $this->assign('zfxx',$zfxx);
        $pay=Db::table("cg_paymethod")->select();
        $this->assign("pay",$pay);
        return $this->fetch('form');

    }

    //录入--非常规和进场
    public function inputhcg(){
        $id = $this->request->param('id');
        if($_POST){
            $data = Request::instance()->post();
            $valid = new Valid([
                'zfpz|支付凭证'=>'require',
                'zfr|支付人'=>'require',
                'zfsj|支付时间'=>'require|dateFormat:Y-m-d',
                'bczfje|本次支付金额'=>'require',
            ]);
            if(false==$valid->check($data)){
                echo json_encode(['success'=>false,'msg'=>$valid->getError()]);die;
            }

            //验证剩余支付金额
            $yzfje = db('ti_hexiao_apply')->where('id',$id)->value('yzfje');
            $tax = db('ti_hexiao_apply')->where('id',$id)->value('tax');
            $tt = db('ti_hexiao_detail')
                ->where([
                    'zfid'=>13,
                    'hexiao_id'=>$id
                ])
                ->field('hexiao_weishui,hexiao_shuilv,dikou')
                ->select();
            $bhje = 0;
            foreach($tt as $k=>$v){
                $bhje += $v['dikou']==1?$v['hexiao_weishui']:($v['hexiao_weishui']*(1+$v['hexiao_shuilv']/100));
            }
            if($data['bczfje']>($tax-$yzfje-$bhje)){
                echo json_encode(['success'=>false,'msg'=>'本次支付金额不能大于可支付金额']);die;
            }
            $data['hx_id'] = $id;
            $bool = db('ti_hexiao_zhifu')->insert($data);
            if(false!==$bool){
                db('ti_hexiao_apply')->where('id',$id)->setInc('yzfje',$data['bczfje']);
                echo json_encode(['success'=>true,'msg'=>'ok']);
            }else{
                echo json_encode(['success'=>false,'msg'=>'数据库错误']);
            }
            die;
        }

        //查询核销基本信息
        $basic = db('ti_hexiao_apply')->where('id',$id)->find();
        $basic['cuser_name'] = db('employee')->where('id',$basic['c_user_id'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['c_user_id'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['c_user_id']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        //补货及补费中金额
        $tt = db('ti_hexiao_detail')
            ->where([
                'zfid'=>13,
                'hexiao_id'=>$id
                ])
            ->field('hexiao_weishui,hexiao_shuilv,dikou')
            ->select();
        $bhje = 0;
        foreach($tt as $k=>$v){
            $bhje += $v['dikou']==1?$v['hexiao_weishui']:($v['hexiao_weishui']*(1+$v['hexiao_shuilv']/100));
        }
        $basic['bhje'] =$bhje;
        $basic['kzfje'] = $basic['tax']-$basic['yzfje']-$basic['bhje'];
        $this->assign('basic',$basic);

        $list = db('cg_ti_hexiao_list t')->join('cg_ti_list t2','t2.id=t.list_id')->join('cg_ti t3','t3.id=t2.ti_id')->where('t.hx_id',$id)->field('*,t.sn tsn,t2.sn t2sn,t3.sn t3sn,t.id tid,t2.id t2id,t3.id t3id,t2.cuser ysqr')->select();
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['shijitourujine'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }
        $this->assign('shijitourujine',$shijitourujine);
        $this->assign('yihexiaojine',$yihexiaojine);
        $this->assign('weishuihexiaojine',$weishuihexiaojine);
        $this->assign('shuijin',$shuijin);
        $this->assign('hanshuihexiaojine',$hanshuihexiaojine);
        $this->assign('shijitourujine2',$shijitourujine2);
        $this->assign('list',$list);

        //支付信息
        $zfxx = db('ti_hexiao_zhifu')->where('hx_id',$id)->select();
        foreach($zfxx as $k=>$v){
            $v['zfr'] = db('employee')->where('id',$v['zfr'])->value('em_name');
            $zfxx[$k] = $v;
        }
        $this->assign('zfxx',$zfxx);
        return $this->fetch('formhcg');
    }

    //查看--非常规和进场
    public function view(){
        $id = $this->request->param('id');

        //查询核销基本信息
        $basic = db('cg_ti_hexiao')->where('id',$id)->find();
        $basic['ctime'] = date('Y-m-d H:i',$basic['ctime']);
        $basic['cuser_name'] = db('employee')->where('id',$basic['cuser'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['cuser'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['cuser']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        //补货及补费中金额
        $basic['bhje'] = db('cg_ti_hexiao_list')
            ->where([
                'zhifufangshi'=>13,
                'hx_id'=>$id
                ])
            ->sum('shijitourujine');
        $basic['kzfje'] = $basic['tax']-$basic['yzfje']-$basic['bhje'];
        $this->assign('basic',$basic);

        $list = db('cg_ti_hexiao_list t')->join('cg_ti_list t2','t2.id=t.list_id')->join('cg_ti t3','t3.id=t2.ti_id')->where('t.hx_id',$id)->field('*,t.sn tsn,t2.sn t2sn,t3.sn t3sn,t.id tid,t2.id t2id,t3.id t3id,t2.cuser ysqr')->select();
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['shijitourujine'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }
        $this->assign('shijitourujine',$shijitourujine);
        $this->assign('yihexiaojine',$yihexiaojine);
        $this->assign('weishuihexiaojine',$weishuihexiaojine);
        $this->assign('shuijin',$shuijin);
        $this->assign('hanshuihexiaojine',$hanshuihexiaojine);
        $this->assign('shijitourujine2',$shijitourujine2);
        $this->assign('list',$list);

        //支付信息
        $zfxx = db('cg_ti_hexiao_zhifu')->where('hx_id',$id)->select();
        foreach($zfxx as $k=>$v){
            $v['zfr'] = db('employee')->where('id',$v['zfr'])->value('em_name');
            $zfxx[$k] = $v;
        }
        $this->assign('zfxx',$zfxx);
        return $this->fetch();
    }
    public function viewcg(){
        $id = $this->request->param('id');

        //查询核销基本信息
        $basic = db('ti_hexiao_apply')->where('id',$id)->find();
        $basic['ctime'] = $basic['created_on'];
        $basic['sn'] = $basic['no'];
        $basic['status'] = $basic['state'];
        $basic['beizhu'] = $basic['remark'];
        $basic['cuser_name'] = db('employee')->where('id',$basic['c_user_id'])->value('em_name');
        $basic['cuser_department'] = db('department_employee t')->join('department t2','t2.id=t.department_id')->where(['employee_id'=>$basic['c_user_id'],'t.index'=>1])->value('t2.de_name');
        $basic['cuser_job'] = db('job_employee t')->join('job t2','t2.id=t.job_id')->where(['employee_id'=>$basic['c_user_id']])->value('t2.job_name');
        $basic['jxs_title'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('title');
        $basic['jxs_direct'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('direct');
        $basic['jxs_cw_bank'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_bank');
        $basic['jxs_cw_banksn'] = db('cg_jxs')->where('id',$basic['jxs_id'])->value('cw_banksn');
        //补货及补费中金额
        $bhje = 0;
        $t = db('ti_hexiao_detail')
            ->where([
                'zfid'=>13,
                'hexiao_id'=>$id
                ])
            ->field('hexiao_weishui,hexiao_shuilv,dikou')
            ->select();
        foreach($t as $k=>$v){
            $bhje += $v['dikou']==1?$v['hexiao_weishui']:($v['hexiao_weishui']*(1+$v['hexiao_shuilv']/100));
        }
        $basic['bhje'] = $bhje;
        $basic['kzfje'] = $basic['tax']-$basic['yzfje']-$basic['bhje'];
        $this->assign('basic',$basic);

        $list = db('ti_hexiao_detail t')->join('ti_apply_detail t2','t2.id=t.detail_id')->join('ti_apply t3','t3.id=t2.apply_id')->where('t.hexiao_id',$id)->field('*,t.no tsn,t2.detail_no t2sn,t3.no t3sn,t.id tid,t2.id t2id,t3.id t3id,t3.c_users_id ysqr')->select();
        $shijitourujine = 0;
        $yihexiaojine = 0;
        $weishuihexiaojine = 0;
        $shuijin = 0;
        $hanshuihexiaojine = 0;
        $shijitourujine2 = 0;
        foreach($list as $k=>$v){
            $shijitourujine += $v['fb_shijiweishui'];
            $yihexiaojine += $v['hx_yihexiao'];
            $weishuihexiaojine += $v['weishuihexiaojine'];
            $shuijin += $v['shuijin'];
            $hanshuihexiaojine += $v['hanshuihexiaojine'];
            $shijitourujine2 += $v['shijitourujine'];
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_id'] = db('cg_net')->where('id',$v['net_id'])->value('system_id');
            $v['system_title'] = db('cg_system')->where('id',$v['system_id'])->value('title');
            $v['brand_title'] = db('pro_brands')->where('id',$v['brand'])->value('brand_name');
            if($v['category']){
                $brand_id = db('pro_category')->where('id',$v['category'])->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['series']){
                $category_id = db('pro_series')->where('id',$v['series'])->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['sku']){
                $series_id = db('pro_sku')->where('id',$v['sku'])->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['product']){
                $sku_id = db('pro_products')->where('id',$v['product'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['tm_id']){
                $sku_id = db('pro_products')->where('id',$v['tm_id'])->value('sku_id');
                $series_id = db('pro_sku')->where('id',$sku_id)->value('series_id');
                $category_id = db('pro_series')->where('id',$series_id)->value('category_id');
                $brand_id = db('pro_category')->where('id',$category_id)->value('brand_id');
                $v['brand_title'] = db('pro_brands')->where('id',$brand_id)->value('brand_name');
            }
            if($v['feetype']){
                $v['feetype_title'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            }
            $v['shijitourujine_title'] = db('cg_paymethod')->where('id',$v['shijitourujine'])->value('title');
            $v['ysqr_title'] = db('employee')->where('id',$v['ysqr'])->value('em_name');
            $list[$k] = $v;
        }
        $this->assign('shijitourujine',$shijitourujine);
        $this->assign('yihexiaojine',$yihexiaojine);
        $this->assign('weishuihexiaojine',$weishuihexiaojine);
        $this->assign('shuijin',$shuijin);
        $this->assign('hanshuihexiaojine',$hanshuihexiaojine);
        $this->assign('shijitourujine2',$shijitourujine2);
        $this->assign('list',$list);

        //支付信息
        $zfxx = db('ti_hexiao_zhifu')->where('hx_id',$id)->select();
        foreach($zfxx as $k=>$v){
            $v['zfr'] = db('employee')->where('id',$v['zfr'])->value('em_name');
            $zfxx[$k] = $v;
        }
        $this->assign('zfxx',$zfxx);
        return $this->fetch();
    }

    //加载人
    public function loadRen(){
        $data = db('employee')->field('id,em_name title')->select();

        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }

    public function selectzfr(){
        return $this->fetch();
    }

    public function listyzf(){
        $mdbh = $this->request->get('mdbh');
        $this->assign('mdbh',$mdbh);
        $fq = $this->request->get('fq');
        $this->assign('fq',$fq);
        $sf = $this->request->get('sf');
        $this->assign('sf',$sf);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $fypz = $this->request->get('fypz');
        $this->assign('fypz',$fypz);

        $where = [];
        $where['yzfje'] = ['exp','>0'];
        if($mdbh){
            $where['sn'] = ['like',"%{$mdbh}%"];
        }
        if($fq){
            $fqids = db('cg_market_zoning')->where('title','like',"%{$fq}%")->column('id');
            $jxsids = db('cg_jxs')->where('zoning','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sf){
            $fqids = db('cg_market_provice')->where('title','like',"%{$sf}%")->column('id');
            $jxsids = db('cg_jxs')->where('provice','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sqr){
            $sqrids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['cuser'] = ['in',$sqrids];
        }
        if($start&&$end){
            $where['ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['ctime'] = ['>',strtotime($end)];
        }

        if($fypz){
            $hxids = db('cg_ti_hexiao_zhifu')->where('zfpz','like',"%{$fypz}%")->column('hx_id');
            $where['id'] = ['in',$hxids];
        }

        $cnt = db('cg_ti_hexiao')->where($where)->count();
        $this->assign('cnt',$cnt);
        $data = db('cg_ti_hexiao')->where($where)->paginate(10,$cnt);
        $page = $data->render();
        $data = $data->items();

        $tax = 0;
        foreach($data as $k=>$v){
            $v['cuser_name'] = db('employee')->where('id',$v['cuser'])->value('em_name');
            $v['jxs_name'] = db('cg_jxs')->where('id',$v['cuser'])->value('title');
            $v['zfsj'] = db('cg_ti_hexiao_zhifu')->where('hx_id',$v['id'])->order('zfsj desc')->value('zfsj');
            $v['zfpz'] = db('cg_ti_hexiao_zhifu')->where('hx_id',$v['id'])->order('zfsj desc')->value('zfpz');
            $data[$k] = $v;
            $tax += $v['tax'];
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        $this->assign('tax',$tax);

        return $this->fetch();
    }
    public function listyzfcg(){
        $mdbh = $this->request->get('mdbh');
        $this->assign('mdbh',$mdbh);
        $fq = $this->request->get('fq');
        $this->assign('fq',$fq);
        $sf = $this->request->get('sf');
        $this->assign('sf',$sf);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $fypz = $this->request->get('fypz');
        $this->assign('fypz',$fypz);

        $where = [];
        $where['tax'] = ['exp','<= `yzfje`'];
        if($mdbh){
            $where['sn'] = ['like',"%{$mdbh}%"];
        }
        if($fq){
            $fqids = db('cg_market_zoning')->where('title','like',"%{$fq}%")->column('id');
            $jxsids = db('cg_jxs')->where('zoning','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sf){
            $fqids = db('cg_market_provice')->where('title','like',"%{$sf}%")->column('id');
            $jxsids = db('cg_jxs')->where('provice','in',$fqids)->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['jxs_id'] = ['in',$jxsids];
        }
        if($sqr){
            $sqrids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['cuser'] = ['in',$sqrids];
        }
        if($start&&$end){
            $where['ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['ctime'] = ['>',strtotime($end)];
        }

        if($fypz){
            $hxids = db('ti_hexiao_zhifu')->where('zfpz','like',"%{$fypz}%")->column('hx_id');
            $where['id'] = ['in',$hxids];
        }

        $cnt = db('ti_hexiao_apply')->where($where)->count();
        $this->assign('cnt',$cnt);
        $data = db('ti_hexiao_apply')->where($where)->field('*,no sn')->paginate(10,$cnt);
        $page = $data->render();
        $data = $data->items();

        $tax = 0;
        foreach($data as $k=>$v){
            $v['cuser_name'] = db('employee')->where('id',$v['c_user_id'])->value('em_name');
            $v['jxs_name'] = db('cg_jxs')->where('id',$v['c_user_id'])->value('title');
            $v['zfsj'] = db('ti_hexiao_zhifu')->where('hx_id',$v['id'])->order('zfsj desc')->value('zfsj');
            $v['zfpz'] = db('ti_hexiao_zhifu')->where('hx_id',$v['id'])->order('zfsj desc')->value('zfpz');
            $data[$k] = $v;
            $tax += $v['tax'];
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        $this->assign('tax',$tax);

        return $this->fetch();
    }
}